package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.DepartmentDataList;
import com.mbc.rmt.entities.Department;
import com.mbc.rmt.entities.DepartmentRpt;

public class IAIMSDepartmentDAOImpls extends DAO<Department> implements IAIMSDepartmentDAO {
	
	public static final String field_departmentkey = "DepartmentKey";
	public static final String field_dept_id = "Dept_ID";
	public static final String field_description = "Description";
	public static final String field_office_id = "Office_Id";

	public IAIMSDepartmentDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "Department";
	}
	
	@Override
	public void create(Department entity) throws SQLException {
		query = insertParam(table_name, new String[] { field_departmentkey, field_dept_id, field_description,
				field_office_id });
		pstmt = conn.prepareStatement(query);
		updateRecord(entity);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(Department entity) throws SQLException {
		query = updateParam(table_name, new String[] { field_departmentkey, field_dept_id, field_description,
				field_office_id }) + " AND " + field_departmentkey + " = " + entity.getDepartment_id();
		pstmt = conn.prepareStatement(query);
		updateRecord(entity);
		pstmt.executeUpdate();
		pstmt.close();
	}
	
	protected void updateRecord(Department entity) throws SQLException {
		int i = 1;
		pstmt.setLong(i++, entity.getDepartment_id());
		pstmt.setString(i++, entity.getDepartment_code());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranch_id());
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " WHERE " + field_departmentkey + " = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public Department findByCode(String code, long branchId)
			throws SQLException {
		Department l_Department = null;
		query = getSelectQuery() + " WHERE " + field_dept_id + " = '" + code + "' AND " + field_office_id + " = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_Department = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Department;
	}

	@Override
	public DepartmentDataList findDepartments(Department cri)
			throws SQLException {
		DepartmentDataList l_DataList = new DepartmentDataList();
		ArrayList<Department> l_entityList = new ArrayList<Department>();
		/*String l_cri = " and (departmentCode like N'%" + cri.getKeyword() + "%'" +
					" or Description like N'%" + cri.getKeyword() + "%')"+
					" and BranchId = " + cri.getBranchId();*/
		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%' ) AND " + field_office_id + " = " + cri.getBranchId();
					
			query = "set rowcount " + cri.getSize() +
				" select * from (select row_number() over(order by " + field_departmentkey + ") as [rowno], * from " + table_name +
				" where 1 = 1" + l_cri + ") " + table_name +
				" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {	
			l_entityList.add(readRecord(rs));
		} 
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(" + field_departmentkey + ") from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public Department getInfo(Department info) throws SQLException {
		Department l_Department = null;
		query = getSelectQuery() + " WHERE " + field_dept_id + " = '" + info.getDepartment_code() + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Department = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Department;
	}

	@Override
	protected Department readRecord(ResultSet rs) throws SQLException {
		Department l_entity = new Department();
		l_entity.setDepartment_id(rs.getLong(field_departmentkey));
		l_entity.setDepartment_code(rs.getString(field_dept_id));
		l_entity.setDescription(rs.getString(field_description));
		l_entity.setBranch_id(rs.getLong(field_office_id));
		
		return l_entity;
	}

	@Override
	public DepartmentDataList getDepartmentList(DepartmentRpt department)
			throws SQLException {
		DepartmentDataList l_DataList = new DepartmentDataList();
		ArrayList<Department> l_DepartmentList = new ArrayList<Department>();
		String l_query = "dbo.sp_cr_DepartmentRpt ?, ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		DepartmentRptParameter(l_pstmt, department);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_DepartmentList.add(readRecordFT(l_rs));
		}
		l_DataList.setEntityList(l_DepartmentList);
		l_rs.close();
		l_pstmt.close();
		return l_DataList;
	}
	
	private void DepartmentRptParameter(CallableStatement l_pstmt,
			DepartmentRpt department) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, department.getFromDepartmentID());
		l_pstmt.setString(i++, department.getToDepartmentID());
		l_pstmt.setLong(i++, department.getBranchID());
	}
	
	private Department readRecordFT(ResultSet rs) throws SQLException {
		Department l_entity = new Department();
		l_entity.setDepartment_code(rs.getString(field_dept_id));
		l_entity.setDescription(rs.getString(field_description));
		
		return l_entity;
	}

	@Override
	public ResultSet prepareDepartmentReport(DepartmentRpt department)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_DepartmentRpt ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		DepartmentRptParameter(l_pstmt, department);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

}
